package com.wxtf.platform.main.sygl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: credentials_recordAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.credentials_recordAction</li>
 * <li>Description: 独生子女父母光荣证登记</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class credentials_recordAction extends LogicBase implements
		CommonsCallProvider {
	private static final Log log = LogFactory
			.getLog(credentials_recordAction.class);

	public credentials_recordAction(String jsonArray) {

	}

	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if ("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if ("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if ("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if ("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if ("cancel".equals(this.getMethodName())) {
				msg = this.cancel(this.getLogicData());
			} else if ("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			}else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = { "tbeocr_id", "tbfi_id", "org_id", "tbeocr_father_name",
			"tbeocr_father_idcard", "tbeocr_father_department",
			"tbeocr_mother_name", "tbeocr_mother_idcard",
			"tbeocr_mother_department", "tbeocr_family_address",
			"tbeocr_child_name", "tbeocr_child_sex", "tbeocr_child_age",
			"tbeocr_child_birthday", "tbeocr_child_idcard", "tbeocr_code",
			"tbeocr_time", "tbeocr_give_time", "tbeocr_fee_param",
			"tbeocr_memo", "reserve1", "reserve2", "reserve3", "reserve4",
			"reserve5", "create_by", "create_time", "last_modify_by",
			"last_modify_time" };

	String[] fieldss = { "tbeocr_id", "tbfi_id",
			"org_id", "fatherid","tbeocr_father_name", "tbeocr_father_idcard",
			"tbeocr_father_department", "trpocfm_father_age",
			"trpocfm_father_seniority", "motherid","tbeocr_mother_name",
			"tbeocr_mother_idcard", "tbeocr_mother_department",
			"trpocfm_mother_age", "trpocfm_mother_seniority",
			"tbeocr_family_address", "tbeocr_child_name","tbeocr_child_sex", "tbfi_children_mnum","tbfi_children_fnum",
			"tbeocr_child_age", "tbeocr_child_birthday", "tbeocr_child_idcard",
			"tbeocr_code", "tbeocr_time", "tbeocr_give_time",
			"tbeocr_fee_param", "tbeocr_memo", "reserve1", "reserve2",
			"reserve3", "reserve4", "reserve5", "create_by", "create_time",
			"last_modify_by", "last_modify_time" };
	
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String strSql = "from tb_family_base f LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN tbe_only_child_record o on f.tbfi_id=o.tbfi_id where e.tbe_employment_forms <> '31' and f.tbfi_children_num = 1 and tbe_status not in('102-4','102-3')";
		//劳务用工和已离职和已退休的员工不能添加
		// 家庭子女数为1个的结果集，
		JSONArray array = JSONArray.fromObject(jsonArray);
		if (array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if ("init".equals(types)) {// 初始查询
				sql =" and e.org_id in (" + user.getCurSubOrgId()+ ") ";
			} else if ("serch1".equals(types)) {// 条件查询
				sql="";
				String orgIds = jsonObject.getString("orgIds");// 机构ID串
				String tbeocr_fee_param = jsonObject.getString("tbe_work_permit");// 核算标准
				String tbe_name = jsonObject.getString("tbe_name");// 父亲姓名
				String mother = jsonObject.getString("mother");// 母亲姓名
				String time1 = jsonObject.getString("time1");// 开始时间
				String time2 = jsonObject.getString("time2");// 结束时间
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)) {
					buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}

				if (StringUtils.isNotBlank(tbeocr_fee_param)) {
					if(!tbeocr_fee_param.equals("3") && tbeocr_fee_param != "") {
						buff.append(" and tbeocr_fee_param = '").append(tbeocr_fee_param).append("'");
					}
				}
				if(StringUtils.isNotBlank(tbe_name)) {
					buff.append(" and e.tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(mother)) {
					buff.append(" and f.tbfi_spouse_name like '%").append(mother.toUpperCase()).append("%'");
				}
				if(StringUtils.isNotBlank(time1)) {
					buff.append(" and tbeocr_time >= '").append(time1).append("'");
				}
				if(StringUtils.isNotBlank(time2)) {
					buff.append(" and tbeocr_time <='").append(time2).append(" 23:59:59'");
				}
				sql += buff.toString();
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		String sorts="";
		if (sortname != null && sortname != null) {
			if(sortname.subSequence(0, 4).equals("tbeo"))
			{
				sorts += " order by o." + sortname + " " + sortorder;
			}
			else if(sortname.subSequence(0, 4).equals("tbfi"))
			{
				sorts += " order by f." + sortname + " " + sortorder;
			}
			else
			{
				sorts += " order by e." + sortname + " " + sortorder;
			}
		}
		

		String countSql = "select count(*) " + strSql+sql;

		String querySql = "SELECT o.tbeocr_id,f.tbfi_id,e.org_id,e.tbe_id as fatherid,"
			+ "e.tbe_name as tbeocr_father_name,e.tbe_idcard as tbeocr_father_idcard,e.reserve1 as tbeocr_father_department,e.tbe_age as trpocfm_father_age,e.tbe_seniority as trpocfm_father_seniority,"
			+ "f.tbe_spouse_id as motherid,f.tbfi_spouse_name as tbeocr_mother_name,f.tbfi_spouse_idcard as tbeocr_mother_idcard,o.tbeocr_mother_department,f.tbfi_spouse_age as trpocfm_mother_age,'0' as trpocfm_mother_seniority,"
			+ "f.tbfi_address as tbeocr_family_address,o.tbeocr_child_name,o.tbeocr_child_sex,f.tbfi_children_mnum,f.tbfi_children_fnum,o.tbeocr_child_age,o.tbeocr_child_birthday,o.tbeocr_child_idcard,o.tbeocr_code,o.tbeocr_time,o.tbeocr_give_time,o.tbeocr_fee_param,"
			+ "o.tbeocr_memo,e.reserve1,o.reserve2,o.reserve3,o.reserve4,o.reserve5,o.create_by,o.create_time,o.last_modify_by,o.last_modify_time "+ strSql+sql+sorts;

//		System.out.println("credent:"+querySql);
		String strJson = AccessDataBaseUtil.query4SQLPagging(fieldss, countSql, querySql, this.getPage(), this.getPagesize(), null);
		int childnum=0;//孩子数
		int emp = 0;//应办证
		int credentied = 0;//已办证
		int credentials = 0;//未办证
		
		String child="select e.tbe_id as fatherid,f.tbe_spouse_id as motherid " +strSql+" and e.tbe_sex='男'"+ sql
		+" union select f.tbe_spouse_id as fatherid,e.tbe_id as motherid " +strSql+" and e.tbe_sex='女'"+ sql;
		List<Map<String, String>> listchild = new ArrayList<Map<String, String>>();
		List<Object[]> list = AccessDataBaseUtil.query(child,null);

		if(list==null)
		{
			childnum=0;
		}else
		{
			if(list.size() > 0)
			{
				for (int i = 0; i < list.size(); i++) {// 行
					Object[] ids=list.get(i);
					Map<String, String> mapfm = new HashMap<String, String>();
					mapfm.put(ids[0].toString(),ids[1].toString());
					//子女数
					if(!listchild.contains(mapfm))
					{
						listchild.add(mapfm);
					}
				}
			}
		}
		String cre="select count(*)" + strSql+ sql;//应办证
		String cred="select count(*)" + strSql+" and o.reserve2<>'1' and tbeocr_id <> ''"+ sql;//已办证
		childnum=listchild.size();
		emp=AccessDataBaseUtil.queryForInt(cre, null);
		credentied=AccessDataBaseUtil.queryForInt(cred, null);
		credentials=emp-credentied;
		
		return strJson.substring(0, strJson.indexOf(","))+",\"child\":\"" + childnum+ "\",\"emp\":\"" + emp + "\","+ "\"credentied\":\"" +credentied+"\"," + "\"credentials\":\"" + credentials + "\""+ strJson.substring(strJson.indexOf(","), strJson.length());
//		System.out.println(System.currentTimeMillis());
//		if (list.equals(null) || list.equals("")) {
//			return "{\"Total\":\"0\",\"child\":\"" + child + "\",\"emp\":\""
//					+ emp + "\",\"credentied\":\"" + credentied + "\","
//					+ "\"credentials\":\"" + credentials + "\",\"Rows\":[]}";
//		} else {
//			jsonArraylist = JSONArray.fromObject(list);
//			json = jsonArraylist.toString();
//			if (list.size() > 0) {
//				emp = list.size();
//				for (int i = 0; i < list.size(); i++) {// 行
//					Map<String, String> map = list.get(i);
//					Map<String, String> mapfm = new HashMap<String, String>();
//					mapfm.put(map.get("fatherid"),map.get("motherid"));
//					//子女数
//					if(!listchild.contains(mapfm))
//					{
//						listchild.add(mapfm);
//					}
//					
//					//已办证
//					if (!map.get("tbeocr_id").equals("")) {
//						credentied += 1;
//					}
//				}
//				child = listchild.size();
//				credentials = emp - credentied;
//				return "{\"Total\":\"" + count[0] + "\",\"child\":\""
//						+ child + "\",\"emp\":\"" + emp
//						+ "\",\"credentied\":\"" + credentied + "\","
//						+ "\"credentials\":\"" + credentials + "\",\"Rows\":"
//						+ json + "}";
//
//			} else {
//				return "{\"Total\":\"0\",\"child\":\"" + child
//						+ "\",\"emp\":\"" + emp + "\",\"credentied\":\""
//						+ credentied + "\"," + "\"credentials\":\""
//						+ credentials + "\",\"Rows\":[]}";
//			}
//		}

	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		//验证是否已经办过证
		String sql_sel="select count(*) from tbe_only_child_record where tbfi_id='"+jsonObject.getString("tbfi_id")+"' ";
		if(AccessDataBaseUtil.queryForInt(sql_sel, null) > 0) {	
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该家庭办理过独生子女证,删除之后才可以重新办理\"}";
		}
		
		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into tbe_only_child_record(tbeocr_id");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		String curTime = FormatUtil.getCurentDateTime();
		obj[25] = user.getUserId();
		obj[26] = curTime;
		obj[27] = user.getUserId();
		obj[28] = curTime;

		for (int i = 0; i < obj.length; i++) {
			buff2.append("'").append(obj[i]).append("',");
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();
		String[] sqls;
		//需要先看看独生子女费用表的数据是否还存在
		String manSql = "select create_time,trpocfm_id,trpocfo_id,trpocfm_fee_option from trp_only_child_fee_options where trpocfm_id in (select trpocfm_id from trp_only_child_fee_management where tbfi_id = ?) order by create_time desc";
		String[] field = {"create_time","trpocfm_id","trpocfo_id","trpocfm_fee_option"};
		List<Map<String, String>> lmanage = AccessDataBaseUtil.query(field,manSql,new Object[] { jsonObject.getString("tbfi_id") });
		if(lmanage.size()>0)
		{
			//该家庭id已经添加过了独生子女费，直接更新
			Map<String, String> map = lmanage.get(0);
			//如果标准没有变化，不需要更新操作
			if(!map.get("trpocfm_fee_option").toString().equals(obj[18]))
			{
				//// 获取插入trp_only_child_fee_options需要的trpocfm_id值
				// 登记表更新的时候，独生子女费用管理情况也要更新
				String sqlman = " update trp_only_child_fee_management set trpocfm_child_name = '"+ obj[9]+ "',trpocfm_child_birthday = '"+ obj[12]+ "',trpocfm_child_sex = '"+ obj[10]+ "',trpocfm_ocr_time = '"+ obj[15]+ "',trpocfm_fee_option = '"+ obj[17]+ "',trpocfm_issue_time = '"+ obj[16]+ "' where trpocfm_id ='"+ map.get("trpocfm_id") + "'";
				//如果是当前月份的话，直接更新[比较年月]
				String time = map.get("create_time").toString().split(" ")[0].substring(0, 7);
				
				Date time1=new Date();
				SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd");   
				String date= myFormatter.format(time1).substring(0, 7); 

				if(time.equals(date))
				{
					String sqlopt = " update trp_only_child_fee_options set trpocfm_fee_option='"+obj[17]+"' where trpocfo_id = '"+map.get("trpocfo_id")+"'";
					sqls = new String[] { sql1, sqlman, sqlopt };
				}
				else
				{
					//如果不是当月的话，需要插入
					String sqlopt = "insert into trp_only_child_fee_options(trpocfo_id,trpocfm_id,trpocfm_fee_option,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) values('"
						+ AccessDataBaseUtil.getPKUUID()+ "','"+ map.get("trpocfm_id")+ "','"+ obj[18]+ "','"+ obj[20]+ "','"+ obj[21]+ "','"+ obj[22]+ "','"+ obj[23]+ "','"+ obj[24]+ "','"+ obj[25]+ "','"+ obj[16]+ "','"+ obj[27]+ "','"+ obj[28] + "')";
					sqls = new String[] {sql1,sqlman, sqlopt };
				}
			}else{sqls = new String[] {sql1};}

		}
		else
		{
			//重新插入费用信息
			String fage = jsonObject.getString("trpocfm_father_age");
			fage = (fage.equals("")) ? "0" : fage;
			String fsen = jsonObject.getString("trpocfm_father_seniority");
			fsen = (fsen.equals("")) ? "0" : fsen;
			String mage = jsonObject.getString("trpocfm_mother_age");
			mage = (mage.equals("")) ? "0" : mage;
			String msen = jsonObject.getString("trpocfm_mother_seniority");
			msen = (msen.equals("")) ? "0" : msen;
			
			String id = AccessDataBaseUtil.getPKUUID();
			String sqlfee1 = "insert into trp_only_child_fee_management(trpocfm_id,org_id,tbfi_id,trpocfm_child_name,trpocfm_child_birthday,trpocfm_child_sex,trpocfm_father_name,trpocfm_father_age,trpocfm_father_seniority,trpocfm_mother_name,trpocfm_mother_age,trpocfm_mother_seniority,trpocfm_ocr_time,trpocfm_fee_option,trpocfm_issue_time,trpocfm_memo,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) "
					+ "values('"+ id+ "','"+ obj[2]+ "','"+ obj[1]+ "','"+ obj[10]+ "','"+ obj[13]+ "','"+ obj[11]+ "','"+ obj[3]+ "','"+ fage+ "','"+ fsen+ "','"+ obj[6]+ "','"+ mage+ "','"+ msen+ "','"+ obj[16]+ "','"+ obj[18]+ "','"+ user.getGlobalMap().get("Child fees")+ "','"+ obj[19]+ "','"+ obj[20]+ "','"+ obj[21]+ "','"+ obj[22]+ "','"+ obj[23]+ "','"+ obj[24]+ "','"+ obj[25]+ "','"+ obj[26]+ "','" + obj[27] + "','" + obj[28] + "')";
			//记录表
			String sqlfee2 = "insert into trp_only_child_fee_options(trpocfo_id,trpocfm_id,trpocfm_fee_option,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) values('"
					+ AccessDataBaseUtil.getPKUUID()+ "','"+ id+ "','"+ obj[18]+ "','"+ obj[20]+ "','"+ obj[21]+ "','"+ obj[22]+ "','"+ obj[23]+ "','"+ obj[24]+ "','"+ obj[25]+ "','"+ obj[16]+ "','"+ obj[27]+ "','"+ obj[28] + "')";
			sqls = new String[]{ sql1, sqlfee1, sqlfee2 };
		}
//for(int i=0;i<sqls.length;i++)
//{
//	//System.out.println(sqls[i]);
//}
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"保存成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();

		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("update tbe_only_child_record set ");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			obj[26] = user.getUserId();
			obj[27] = curTime;
			buff.append(fields[j]).append(" = ").append("'").append(obj[j - 1]).append("',");
		}
		buff.deleteCharAt(buff.length() - 1).append(" where tbeocr_id = '" + jsonObject.getString("tbeocr_id") + "'");
		
		//需要先看看独生子女费用表的数据是否还存在
		String manSql = "select create_time,trpocfm_id,trpocfo_id,trpocfm_fee_option from trp_only_child_fee_options where trpocfm_id in (select trpocfm_id from trp_only_child_fee_management where tbfi_id = ?) order by create_time desc";
		String[] field = {"create_time","trpocfm_id","trpocfo_id","trpocfm_fee_option"};
		List<Map<String, String>> lmanage = AccessDataBaseUtil.query(field,manSql,new Object[] { jsonObject.getString("tbfi_id") });
		String[] sqls;
		if(lmanage.size()>0)
		{
			Map<String, String> map = lmanage.get(0);
			// 登记表更新的时候，独生子女费用管理情况也要更新
			String sqlman = " update trp_only_child_fee_management set trpocfm_child_name = '"+ obj[9]+ "',trpocfm_child_birthday = '"+ obj[12]+ "',trpocfm_child_sex = '"+ obj[10]+ "',trpocfm_ocr_time = '"+ obj[15]+ "',trpocfm_fee_option = '"+ obj[17]+ "' where trpocfm_id ='"+ map.get("trpocfm_id") + "'";

			//如果标准没有变化，不需要更新操作
			if(!map.get("trpocfm_fee_option").toString().equals(obj[17]))
			{
				//如果是当前月份的话，直接更新标准[比较年月]
				String time = map.get("create_time").toString().split(" ")[0].substring(0, 7);
				
				Date time1=new Date();
				SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd");   
				String date= myFormatter.format(time1).substring(0, 7); 

				if(time.equals(date))
				{
					String sqlopt = " update trp_only_child_fee_options set trpocfm_fee_option='"+obj[17]+"' where trpocfo_id = '"+map.get("trpocfo_id")+"'";
					sqls = new String[] { buff.toString(), sqlman, sqlopt };
				}
				else
				{
					//如果不是当月的话，需要插入
					String sqlopt = "insert into trp_only_child_fee_options(trpocfo_id,trpocfm_id,trpocfm_fee_option,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) values('"
						+ AccessDataBaseUtil.getPKUUID()+ "','"+map.get("trpocfm_id")+ "','"+ obj[17]+ "','"+ obj[19]+ "','"+ obj[20]+ "','"+ obj[21]+ "','"+ obj[22]+ "','"+ obj[23]+ "','"+ obj[24]+ "','"+ obj[25]+ "','"+ obj[26]+ "','"+ obj[27] + "')";
					sqls = new String[] { buff.toString(), sqlman, sqlopt };
				}
			}else{sqls = new String[] { buff.toString(), sqlman};}

		}
		else
		{
			//重新插入费用信息
			String fage = jsonObject.getString("trpocfm_father_age");
			fage = (fage.equals("")) ? "0" : fage;
			String fsen = jsonObject.getString("trpocfm_father_seniority");
			fsen = (fsen.equals("")) ? "0" : fsen;
			String mage = jsonObject.getString("trpocfm_mother_age");
			mage = (mage.equals("")) ? "0" : mage;
			String msen = jsonObject.getString("trpocfm_mother_seniority");
			msen = (msen.equals("")) ? "0" : msen;
			
			String id = AccessDataBaseUtil.getPKUUID();
			String sqlfee1 = "insert into trp_only_child_fee_management(trpocfm_id,org_id,tbfi_id,trpocfm_child_name,trpocfm_child_birthday,trpocfm_child_sex,trpocfm_father_name,trpocfm_father_age,trpocfm_father_seniority,trpocfm_mother_name,trpocfm_mother_age,trpocfm_mother_seniority,trpocfm_ocr_time,trpocfm_fee_option,trpocfm_issue_time,trpocfm_memo,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) "
					+ "values('"+ id+ "','"+ obj[1]+ "','"+ obj[0]+ "','"+ obj[9]+ "','"+ obj[12]+ "','"+ obj[10]+ "','"+ obj[2]+ "','"+ fage+ "','"+ fsen+ "','"+ obj[5]+ "','"+ mage+ "','"+ msen+ "','"+ obj[15]+ "','"+ obj[17]+ "','"+ user.getGlobalMap().get("Child fees")+ "','"+ obj[18]+ "','"+ obj[19]+ "','"+ obj[20]+ "','"+ obj[21]+ "','"+ obj[22]+ "','"+ obj[23]+ "','"+ obj[24]+ "','"+ obj[25]+ "','" + obj[26] + "','" + obj[27] + "')";
			//记录表
			String sqlfee2 = "insert into trp_only_child_fee_options(trpocfo_id,trpocfm_id,trpocfm_fee_option,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) values('"
				+ AccessDataBaseUtil.getPKUUID()+ "','"+ id+ "','"+ obj[17]+ "','"+ obj[19]+ "','"+ obj[20]+ "','"+ obj[21]+ "','"+ obj[22]+ "','"+ obj[23]+ "','"+ obj[24]+ "','"+ obj[25]+ "','"+ obj[26]+ "','"+ obj[27] + "')";
			
			sqls = new String[] { buff.toString(), sqlfee1, sqlfee2 };
		}
//		for(int i=0;i<sqls.length;i++)
//		{
//			System.out.println(sqls[i]);
//		}
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");

		String[] ids = id.split(",");
		String sqldel = "";
		for (int i = 0; i < ids.length - 1; i = i + 2) {
			sqldel = sqldel
					+ "delete from tbe_only_child_record where tbeocr_id = '"
					+ ids[i].split(":")[0] + "'" + ",";
			sqldel = sqldel
			+ "delete from trp_only_child_fee_management where tbfi_id = '"
			+ ids[i + 1].split(":")[0] + "'" + ",";
	sqldel = sqldel
			+ "delete from trp_only_child_fee_options where trpocfm_id in (select trpocfm_id from trp_only_child_fee_management where tbfi_id = '"
			+ ids[i + 1].split(":")[0] + "')" + ",";
			//判断该证是否已经存在多条记录
//			String strCount = "select count(*) from trp_only_child_fee_options where trpocfm_id in (select trpocfm_id from trp_only_child_fee_management where tbfi_id = ?)";
//			if(AccessDataBaseUtil.queryForInt(strCount, new Object[] {ids[i + 1].split(":")[0]}) >1 ) {
//				return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该证件发放标准存在变更记录，不能进行删除\"}";
//			}else
//			{
//				sqldel = sqldel
//						+ "delete from trp_only_child_fee_management where tbfi_id = '"
//						+ ids[i + 1].split(":")[0] + "'" + ",";
//				sqldel = sqldel
//						+ "delete from trp_only_child_fee_options where trpocfm_id in (select trpocfm_id from trp_only_child_fee_management where tbfi_id = '"
//						+ ids[i + 1].split(":")[0] + "')" + ",";
//			}
			
		}
		String[] sqls = sqldel.split(",");
		if(sqldel.equals(""))
		{
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"证件还没有办理，不能进行删除\"}";
		}
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"删除成功\"";
	}

	/**
	 * 描述：退证
	 * 
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn
	 * @author wangli
	 */
	public Object cancel(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");

		String[] ids = id.split(",");
		String sqldel = "";
		for (int i = 0; i < ids.length - 1; i = i + 2) {
			if(!ids[i].split(":")[0].equals(""))
			{
				sqldel = sqldel
						+ "update tbe_only_child_record set reserve2='1' where tbeocr_id = '"+ ids[i].split(":")[0] + "'" + ",";
			}
//			sqldel = sqldel
//					+ "delete from trp_only_child_fee_management where tbfi_id = '"+ ids[i + 1].split(":")[0] + "'" + ",";
			//将已发放的总金额保存起来
			if(!ids[i + 1].split(":")[0].equals(""))
			{
				int totals=0;
				String manage="select trpocfm_id from trp_only_child_fee_management where tbfi_id = '"+ ids[i + 1].split(":")[0] + "'";
				//统计总金额
				List<Object> listtotal = AccessDataBaseUtil.query4List(manage,null);
				if (listtotal.size() > 0) {
					for (int n = 0; n < listtotal.size(); n++) {// 行
						int sum=0;
						Object trpocfm_id=listtotal.get(n);
						String sqloptions="Select trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+trpocfm_id.toString()+"' order by create_time";
						//System.out.println("sqloptions:"+sqloptions);
						List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
						//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
						for (int j = listopt.size()-1; j >=0 ; j--) {// 行
							int option = 0;
							if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
								option = 5;
							} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
								option = 10;
							}
							//System.out.println("option:"+option);
							if(j == listopt.size() - 1) {
								SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
								Date dd = new Date();
								int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
								sum += month * option;
								//System.out.println("sum:"+sum+"="+month+"*"+option);

								if(listopt.size()==2)
								{
									if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
										option = 5;
									} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
										option = 10;
									}
									int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
									sum += mon * option;
									//System.out.println("sum:"+sum+"="+mon+"*"+option);
								}
							}
							if(listopt.size()!=2)
							{
								if(j >= 1) {
									int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
									sum += month * option;
									//System.out.println("sum:"+sum+"="+month+"*"+option);
								}
							}
						}
						totals += sum;
						sqldel = sqldel
						+ "update trp_only_child_fee_management set reserve2='"+totals+"' where trpocfm_id = '"+trpocfm_id+ "'" + ",";
					}
				}
			}
//			sqldel = sqldel
//					+ "delete from trp_only_child_fee_options where trpocfm_id in (select trpocfm_id from trp_only_child_fee_management where tbfi_id = '"
//					+ ids[i + 1].split(":")[0] + "')" + ",";
		}
		String[] sqls = sqldel.split(",");
//		System.out.println(sqls.length+":11:"+sqldel);
		if(sqldel.equals(""))
		{
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"证件还没有办理，不能退证\"}";
		}
//		for(int i=0;i<sqls.length;i++)
//		{
//			System.out.println(sqls[i]);
//		}
		//return "\"删除成功\"";
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"退证成功\"";
	}

	/**
	 * 描述：expData 人员导出
	 * 
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute(
				"userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("SELECT case o.reserve2 when '1' then '证件已注销' when tbeocr_child_birthday >= DATE_SUB(curdate(),INTERVAL 18 YEAR) then '已满18岁' end,e.reserve1,e.tbe_name as tbeocr_father_name,e.tbe_idcard as tbeocr_father_idcard,e.reserve1 as tbeocr_father_department,")
			.append("f.tbfi_spouse_name as tbeocr_mother_name,f.tbfi_spouse_idcard as tbeocr_mother_idcard,f.tbfi_spouse_department as tbeocr_mother_department,")
			.append("f.tbfi_address as tbeocr_family_address,o.tbeocr_child_name,case o.tbeocr_child_sex when '0' then '男' when '1' then '女' else ' ' end,o.tbeocr_child_birthday,o.tbeocr_child_idcard,o.tbeocr_code,o.tbeocr_time,case o.tbeocr_fee_param when '0' then '五元' when '1' then '十元' else ' 无' end,")
			.append("o.tbeocr_memo from tb_family_base f LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN tbe_only_child_record o on f.tbfi_id=o.tbfi_id where ((e.tbe_employment_forms <> '31' and f.tbfi_children_num = 1 and tbe_status not in('102-4','102-3')) or tbeocr_id <> '')");
		
		String orgIds = jsonObject.getString("orgIds");// 机构ID串
		String tbeocr_fee_param = jsonObject.getString("tbe_work_permit");// 核算标准
		String tbe_name = jsonObject.getString("tbe_name");// 父亲姓名
		String mother = jsonObject.getString("mother");// 母亲姓名
		String time1 = jsonObject.getString("time1");// 母亲姓名
		String time2 = jsonObject.getString("time2");// 母亲姓名

		if (StringUtils.isNotBlank(orgIds)) {
			buff.append(" and e.org_id in (").append(
					user.getCurSubOrgIds(orgIds)).append(")");
		}

		if (StringUtils.isNotBlank(tbeocr_fee_param)) {
			if (!tbeocr_fee_param.equals("3") && tbeocr_fee_param != "") {
				buff.append(" and tbeocr_fee_param = '").append(
						tbeocr_fee_param).append("'");
			}
		}
		if (StringUtils.isNotBlank(tbe_name)) {
			buff.append(" and tbeocr_father_name like '%").append(tbe_name).append("%'");
		}
		if (StringUtils.isNotBlank(mother)) {
			buff.append(" and tbeocr_mother_name like '%").append(mother.toUpperCase()).append("%'");
		}
		if (StringUtils.isNotBlank(time1)) {
			buff.append(" and tbeocr_time >= '").append(time1).append("'");
		}
		if (StringUtils.isNotBlank(time2)) {
			buff.append(" and tbeocr_time <='").append(time2).append(" 23:59:59'");
		}
		buff.append("order by o.last_modify_time desc");
		
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);

		String url = this.getRequest().getSession().getServletContext()
				.getRealPath("/jsp/app/xls/");
		String model = url + "/model/record.xls";
		String file = url + "/file/record.xls";
		String rfile = "jsp/app/xls/file/record.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if (list.size() > 0) {
			XLSUtils.writeXls(list, fto, "办证登记一览表", 1);// 写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}

		return "\"" + rfile + "\"";
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}
